﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;

namespace SpectationClient.SQLCommandBuilder {
    public class Condition: AbstractCondition {
        private enum maxValues:byte{
            zero,
            one,
            unlimited
        }


        /// <summary>
        /// Operator that describes the key the column should have against the input value or values.
        /// </summary>
        public enum Op: byte { 
            /// <summary>
            /// '=', implicit IN (x,y,z,...)
            /// </summary>
            EQ,
            /// <summary>
            /// 'IN'
            /// </summary>
            LT,
            /// <summary>
            /// '<=', implicit <= GREATEST(x,y,z,...)
            /// </summary>
            LE,
            /// <summary>
            /// >  , implicit > GREATEST (x,y,z,...)
            /// </summary>
            GT, 
            /// <summary>
            /// >= , implicit >= LEAST (x,y,z,...)
            /// </summary>
            GE, 
            /// <summary>
            /// != , same like NOT EQUAL, implicit NOT IN (x,y,z,...)
            /// </summary>
            NE,  
            /// <summary>
            /// no values allowed to add
            /// </summary>
            IS_NULL,
            /// <summary>
            /// Matches a regular expression, only one value allowed to add
            /// </summary>
            LIKE,
            LIKE_CaseInsensitive,
            SIMILAR_TO,
            /// <summary>
            /// Matches not to a regular expression, only one value allowed to add
            /// </summary>
            MATCHES,
            MATCHES_CaseInsensitive,
        }


        



        private bool is_positive = false;
        private String column;
        private List<Object> values;
        private Op conditionOperator;
        private maxValues nMaxValue;

        public Condition(String column, Op conditionOperator, NpgsqlCommand cmd) {
            init(column, conditionOperator, true);
            this.Add(cmd);
        }

        # region constructors with generic lists
        public Condition(String column, Op conditionOperator, List<Object> columnValues) {
            this.initGeneric<Object>(column, conditionOperator, columnValues);
        }

        public Condition(String column, Op conditionOperator, List<Int64> columnValues) {
            this.initGeneric<Int64>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<Int32> columnValues) {
            this.initGeneric<Int32>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<Int16> columnValues) {
            this.initGeneric<Int16>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<Double> columnValues) {
            this.initGeneric<Double>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<Single> columnValues) {
            this.initGeneric<Single>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<String> columnValues) {
            this.initGeneric<String>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, List<Boolean> columnValues) {
            this.initGeneric<Boolean>(column, conditionOperator, columnValues);
        }
        # endregion

        # region constructors for generic arrays
        public Condition(String column, Op conditionOperator, Object[] columnValues) {
            this.initGeneric<Object>(column, conditionOperator, columnValues);
        }

        public Condition(String column, Op conditionOperator, Int64[] columnValues) {
            this.initGeneric<Int64>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, Int32[] columnValues) {
            this.initGeneric<Int32>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, Int16[] columnValues) {
            this.initGeneric<Int16>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, Double[] columnValues) {
            this.initGeneric<Double>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, Single[] columnValues) {
            this.initGeneric<Single>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, String[] columnValues) {
            this.initGeneric<String>(column, conditionOperator, columnValues);
        }
        public Condition(String column, Op conditionOperator, Boolean[] columnValues) {
            this.initGeneric<Boolean>(column, conditionOperator, columnValues);
        }
        # endregion

        # region private generic init methods
        private void initGeneric<T>(String column, Op conditionOperator, T[] columnValues) {
            init(column, conditionOperator, true);
            if(this.nMaxValue == maxValues.zero) throw new Exception("Adding values is not allowed when using this operator");
            this.Add<T>(columnValues);
        }
        private void initGeneric<T>(String column, Op conditionOperator, List<T> columnValues) {
            init(column, conditionOperator, true);
            if(this.nMaxValue == maxValues.zero) throw new Exception("Adding values is not allowed when using this operator");
            this.Add<T>(columnValues);
        }

        #endregion
        public Condition(String column, Op conditionOperator, Object columnValue) {
            init(column, conditionOperator, true);
            if(this.nMaxValue == maxValues.zero) throw new Exception("Adding values is not allowed when using this operator");
            this.Add(columnValue);
        }
        public Condition(String column, bool is_positive, Op conditionOperator, Object columnValue) {
            init(column, conditionOperator, is_positive);
            if(this.nMaxValue == maxValues.zero) throw new Exception("Adding values is not allowed when using this operator"); 
            this.Add(columnValue);
        }
        public Condition(String column, Op conditionOperator) {
            init(column, conditionOperator, true);
            this.conditionOperator = conditionOperator;
        }
        public Condition(String column, bool is_positive, Op conditionOperator) {
            init(column, conditionOperator, is_positive);
            this.conditionOperator = conditionOperator;
        }

        public Condition(String column, bool isNULL) {
            this.conditionOperator = Op.IS_NULL;
            this.init(column, this.conditionOperator, isNULL);
        }

        private void init(String column, Op conditionOperator, bool is_positive) {
            values = new List<object>();
            this.is_positive = is_positive;
            this.column = column;
            this.conditionOperator = conditionOperator;
            this.nMaxValue = this.getMaxValues(conditionOperator);
        }


       

        private maxValues getMaxValues(Op op) {
            maxValues mv = maxValues.unlimited;
            switch(op) { 
                case Op.IS_NULL: mv = maxValues.zero; break;
                case Op.MATCHES:
                case Op.MATCHES_CaseInsensitive:
                case Op.SIMILAR_TO:
                case Op.LIKE:
                case Op.LIKE_CaseInsensitive: mv = maxValues.one; break;
                default: mv = maxValues.unlimited; break;
            }
            return mv;
        }

        public override bool IsEmpty {
            get {
                return this.nMaxValue != maxValues.zero && this.values.Count == 0;
            } 
        }

        public String Column { get { return this.column; } }
        public Op Operator { get { return this.conditionOperator; } }
        public int Count { get { return this.values.Count; } }


        public void Add(NpgsqlCommand cmd) {
            if(!(this.Operator == Op.EQ)) throw new Exception("A command can be added as argument of an column EQ or IN (<commandresult>) or column NOT EQ or NOT IN (<commandresult>) only");

            if(cmd != null && cmd.CommandText.Trim().Length > 0) {
               
                    if(this.values.Count >= 1) throw new Exception("Only one command can be added to give a result a condition matches/unmatches on");
                    this.nMaxValue = maxValues.one;
                    values.Add(cmd);
                
            }
        }


        public void Add<T>(List<T> objects) {
            foreach(T item in objects) {
                this.Add(item);
            }
        }

        public void Add<T>(T[] objects) {
            foreach(T item in objects) {
                this.Add(item);
            }
        }

        public void Add(Object obj){
            if(obj == null || obj == DBNull.Value) return;

            Type t = obj.GetType();
            if(t.IsGenericType) {
                throw new Exception("Can not add this generic type");
                /*
                if(relatedColumnType.GetGenericTypeDefinition() == typeof(List<>)) {
                   
                    Type gt = relatedColumnType.GetGenericTypeDefinition();
                    Type itemType = relatedColumnType.GetGenericArguments()[0];
                    IList column = (IList)obj;
                    List<Object> ol = (List<Object>) obj;
                    Type[] args = relatedColumnType.GetGenericArguments();
                    String s ="";
                } else {
                    throw new Exception("unhandled generic type definition");
                }*/

            } else {

                if(this.nMaxValue == maxValues.zero) {
                    throw new Exception("adding object is not allowed with this operator");
                }
                if(obj != null && obj != DBNull.Value && !values.Contains(obj)) {
                    if(values.Count >= 1 && this.nMaxValue == maxValues.one) throw
                        new Exception("statement can be used with one object only");
                    if(obj.GetType() == typeof(string)) {

                        values.Add(String.Format("{0}", obj));
                    } else {
                        values.Add(obj);
                    }
                }
            }
        }

        public static Condition.Op getStringComparer(bool matchExact, bool caseSensitive) {
            Condition.Op cop;
            if(matchExact) {
                cop = (caseSensitive) ? Condition.Op.LIKE : Condition.Op.LIKE_CaseInsensitive;
            } else {
                cop = (caseSensitive) ? Condition.Op.MATCHES : Condition.Op.MATCHES_CaseInsensitive;
            }
            return cop;
        }

        private static String getOperatorString(bool is_true, Op op, int valueCount) {
            String str = null;
            if(valueCount <= 1) {
                switch(op) {
                    case Op.IS_NULL: str = (is_true) ? "IS NULL" : "IS NOT NULL"; break;
                    case Op.EQ: str = (is_true) ? "=" : "!="; break;
                    case Op.GE: str = (is_true) ? ">=" : "<"; break;
                    case Op.GT: str = (is_true) ? ">" : "<="; break;
                    case Op.LE: str = (is_true) ? "<=" : ">"; break;
                    case Op.LT: str = (is_true) ? "<" : ">="; break;
                    case Op.NE: str = (is_true) ? "!=" : "="; break;
                    case Op.LIKE: str = (is_true) ? "LIKE" : "NOT LIKE"; break;
                    case Op.LIKE_CaseInsensitive: str = (is_true) ? "ILIKE" : "NOT ILIKE"; break;
                    
                    case Op.MATCHES: str = (is_true) ? "~" : "!~"; break;
                    case Op.MATCHES_CaseInsensitive: str = (is_true) ? "~*" : "!~*"; break;
                    case Op.SIMILAR_TO: str = (is_true) ? "SIMILAR TO" : "NOT SIMILAR TO"; break;
                    default: throw new NotImplementedException();
                }
            } else {
                switch(op) {
                    case Op.EQ: str = (is_true) ? "IN" : "NOT IN"; break;
                    case Op.NE: str = (is_true) ? "NOT IN" : "IN"; break;
                    default: throw new NotImplementedException();
                }
            }
            if(str == null) throw new NotImplementedException();
            return str;
        }

        public override NpgsqlCommand getCmd() {
            return this.getCmd("");
        }
        public override NpgsqlCommand getCmd(String prefix) {
            
            NpgsqlCommand cmd = new NpgsqlCommand();
            String opStr = Condition.getOperatorString(this.is_positive, this.Operator, this.values.Count);
            String basename = String.Format("@{0}{1}", this.column, prefix);


            String columnName = this.column;
            if(!columnName.Contains('.') && columnName != columnName.ToLower()) columnName = "'"+columnName+"'";
            if(this.nMaxValue == maxValues.zero){

                cmd.CommandText = String.Format("{0} {1}", columnName, opStr);
            }else if(this.nMaxValue == maxValues.one ||
                     this.nMaxValue == maxValues.unlimited && this.values.Count == 1){
                Object item = this.values[0];
                NpgsqlCommand subCmd = item as NpgsqlCommand;
                
                if(subCmd != null){
                    if(this.is_positive) {
                        //is equal or in the the sub_command result
                        cmd.CommandText = String.Format("{0} IN (", columnName, opStr);
                    } else {
                        cmd.CommandText = String.Format("{0} NOT IN (", columnName, opStr);
                    }
                    CommandBuilder.appendCmd(ref cmd, subCmd);
                    cmd.CommandText += ")";
                }else {
                    cmd.CommandText = String.Format("{0} {1} {2}", columnName, opStr, basename);
                    cmd.Parameters.AddWithValue(basename, item);
                }

            }else{
                                 List<String> toCombine = new List<string>();
                    for(int i = 0; i < values.Count; i++) {
                        String parameterName = String.Format("{0}{1}", basename, i);
                        toCombine.Add(parameterName);
                        cmd.Parameters.AddWithValue(parameterName, values[i]);
                    }
                    String parameterSet = TextHelper.combine(toCombine, ", ");
                    cmd.CommandText = String.Format("{0} {1} ({2})", columnName, opStr, parameterSet);
                
            }
            return cmd;
        }
    }
}
